<?php defined('SYSPATH') or die('No direct script access.');

class Channels_Model extends Model{

	protected $temp_filename;

	public function __construct() {
		$this->temp_filename = '';
		$this->db = Database::instance();
	}

	public function count_channels(){
		$query = $this->db->select('id')->get('channels');
		return $query->count();
	}

	public function browse($limit, $offset)
	{
		$sql = '
			SELECT * 
			FROM channels 
			ORDER BY title
			LIMIT ?, ?;
    	';
		$result = $this->db->query($sql, array($offset, $limit))->result(FALSE);
		return $result;
	}

	public function loads(){

		$sql = 'SELECT *
		FROM channels 
		ORDER BY title';

		$query = $this->db->query($sql);
		return $query->result(FALSE);
	}

	public function load($id) {
		$sql = '
			SELECT * 
			FROM channels 
			WHERE id=?;
    	';
		$query = $this->db->query($sql, array($id));
		return $query->result(FALSE);
	}

	public function get_channels_user($user_id){
		$sql = "
			SELECT *
			FROM
			  `channels`
			WHERE
			  `channels`.owner_id = ?;";
		$query = $this->db->query($sql,array($user_id));
		return $query->result(FALSE);
	}
	
	public function get_playlist_in_channel($user_id,$channel_id){
		$sql = "
				SELECT *,
					(
 						SELECT count(*)
 						FROM channels_playlists cp  
 						WHERE cp.playlist_id=p.id
 						AND cp.owner_id=? AND cp.channel_id=?
					) as owner
				FROM playlists p;";
		$query = $this->db->query($sql,array($user_id,$channel_id));
		return $query->result(FALSE);
	}

	public function save($data,$mode="add"){
		$this->db->query('BEGIN');
		try {
			if ($mode=='add') {
				$sql = 'INSERT INTO channels(owner_id,title, description, channel_picture, created) VALUES(?,?,?,?,CURRENT_TIMESTAMP())';
				$query = $this->db->query($sql, array($data['ownerId'],$data['title'], $data['description'], $data['logo']));
			}
			else {
				if (empty($data['logo'])) {
					//					Kohana::log("debug","UPDATE");
					$sql = 'UPDATE channels SET title=?, description=?, updated=CURRENT_TIMESTAMP() WHERE id=?;';
					$query = $this->db->query($sql, array($data['title'], $data['description'], $data['id']));
				}else{
					//					Kohana::log("debug","UPDATE with picture");
					$sql = 'UPDATE channels SET title=?, description=?, channel_picture=?, updated=CURRENT_TIMESTAMP() WHERE id=?;';
					$query = $this->db->query($sql, array($data['title'], $data['description'], $data['logo'], $data['id']));
				}
			}
			$this->db->query('COMMIT');
			return true;
		}
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}
	}

	public function save2($data,$playlists,$mode="add"){
		$this->db->query('BEGIN');
		try {
			if ($mode=='add') {
				$sql = 'INSERT INTO channels(owner_id,title, description, channel_picture, created) VALUES(?,?,?,?,CURRENT_TIMESTAMP())';
				$query = $this->db->query($sql, array($data['ownerId'],$data['title'], $data['description'], $data['logo']));

				$ch_id = mysql_insert_id();
				Kohana::log("debug","Channel ID: ".$ch_id);

				if (!empty($playlists)) {
					foreach ($playlists as $pl) {
						$sql = 'INSERT INTO channels_playlists(owner_id, channel_id, playlist_id, created) VALUES(?,?,?,CURRENT_TIMESTAMP())';
						$query2 = $this->db->query($sql, array($data['ownerId'],$ch_id, $pl));
					}
				}
			}
			else {
				if (empty($data['logo'])) {
					Kohana::log("debug","UPDATE");
					$sql = 'UPDATE channels SET title=?, description=?, updated=CURRENT_TIMESTAMP() WHERE id=?;';
					$query = $this->db->query($sql, array($data['title'], $data['description'], $data['id']));

					//delete old playlist before insert
					$sql = "DELETE FROM channels_playlists WHERE channel_id=?;";
					$query2 = $this->db->query($sql,array($data['id']));

					if (!empty($playlists)) {
						foreach ($playlists as $pl) {
							$sql = 'INSERT INTO channels_playlists(owner_id, channel_id, playlist_id, created) VALUES(?,?,?,CURRENT_TIMESTAMP())';
							$query3 = $this->db->query($sql, array($data['ownerId'],$data['id'], $pl));
						}
					}
				}else{
					Kohana::log("debug","UPDATE with picture");
					$sql = 'UPDATE channels SET title=?, description=?, channel_picture=?, updated=CURRENT_TIMESTAMP() WHERE id=?;';
					$query = $this->db->query($sql, array($data['title'], $data['description'], $data['logo'], $data['id']));

					//delete old playlist before insert
					$sql = "DELETE FROM channels_playlists WHERE channel_id=?;";
					$query2 = $this->db->query($sql,array($data['id']));

					if (!empty($playlists)) {
						foreach ($playlists as $pl) {
							$sql = 'INSERT INTO channels_playlists(owner_id, channel_id, playlist_id, created) VALUES(?,?,?,CURRENT_TIMESTAMP())';
							$query3 = $this->db->query($sql, array($data['ownerId'],$data['id'], $pl));
						}
					}						
				}
			}
			$this->db->query('COMMIT');
			return true;
		}
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}
	}

	public function delete($id) {
		$this->db->query('BEGIN');
		try {
			$sql = 'DELETE FROM channels WHERE id=?;';

			$query = $this->db->query($sql, array($id));

			$this->db->query('COMMIT');

			return true;
		}
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}
	}

	public function file_validate($file_name) {
		$_FILES = Validation::factory($_FILES)
		->add_rules('picture',
								'upload::valid', 
								'upload::type[gif,jpg,png]', 
								'upload::size[1M]');
			
		if ($_FILES->validate())
		{
			$config = Kohana::config('hsm');

			// Temporary file name
			$this->temp_filename = upload::save($file_name);
			if (file_exists($this->temp_filename)) {
				// Resize, sharpen, and save the image
				Image::factory($this->temp_filename)
				->resize($config['image_max_width'], $config['image_max_height'], Image::AUTO)
				->save($config['upload_club'].basename($this->temp_filename));

				// Remove the temporary file
				unlink($this->temp_filename);
				$this->temp_filename = $config['upload_club'].basename($this->temp_filename);
			}
			else {
				$this->temp_filename = '';
			}
		}
		return $_FILES;
	}

	public function get_image_fileneme($id) {
		$sql = '
			SELECT channel_picture 
			FROM channels 
			WHERE id=?;
    	';
		$query = $this->db->query($sql, array($id));
		return $query->current()->channel_picture;
	}

	public function get_filename() {
		return $this->temp_filename;
	}

}
